from tensorflow.python.data.experimental.ops.testing import sleep

from service.simple_service import update_day_his
from utils.data_source import query, update, engine
from utils.data_utils import data_update

import akshare as ak

if __name__ == '__main__':
    try:
        update_day_his()
        dataa = query("""select distinct data from history order by data desc limit 1 offset 0""")["data"].values
        for dta in dataa:
            sys_set = set()
            for y in range(3, 20):
                x_sql = f"""with
                history_temp as ( select * from history where data <= '{dta}'),
                datah as(select data,ROW_NUMBER() OVER () AS rn from (select data, ROW_NUMBER() OVER (order by data desc) AS rn from history_temp group by data) a where rn%5 = 1 limit {y})
                ,
                jj as (select * from (select name,symbol,count(1) as count from (select (b.close - c.close) qs,b.* from (select  a.data,b.data as data1 from datah a left join datah b on a.rn = b.rn+1) a left join history_temp b on a.data = b.data
                left join history_temp c on a.data1 = c.data
                where b.name = c.name) a where qs <= 0  group by name,symbol) a )
                select a.count,b.* from jj as a inner join company b on a.symbol = b.代码 and a.count = {y - 1}"""
                x_sys = query(x_sql)
                if len(sys_set) == 0:
                    for xx in range(len(x_sys)):
                        sys_set.add(x_sys.代码[xx])
                else:
                    for xx in range(len(x_sys)):
                        if x_sys.代码[xx] in sys_set:
                            sys_set.remove(x_sys.代码[xx])

            x_sql = f"""with
                 history_temp as ( select * from history where data <= '{dta}'),
                datah as(select data,ROW_NUMBER() OVER () AS rn from (select data, ROW_NUMBER() OVER (order by data desc) AS rn from history_temp group by data) a where rn%5 = 1 limit 20)
                ,
                jj as (select * from (select name,symbol,count(1) as count from (select (b.close - c.close) qs,b.* from (select  a.data,b.data as data1 from datah a left join datah b on a.rn = b.rn+1) a left join history_temp b on a.data = b.data
                left join history_temp c on a.data1 = c.data
                where b.name = c.name) a where qs <= 0  group by name,symbol) a )
                select a.count,b.* from jj as a inner join company b on a.symbol = b.代码 order by a.count"""
            x_sys = query(x_sql)
            sys_ss = set()
            count = 0
            for xx in range(len(x_sys)):
                print(x_sys['count'][xx])
                print(len(sys_ss))
                if len(sys_ss) > 8 and x_sys['count'][xx] > count:
                    break
                count = x_sys['count'][xx]
                if x_sys.代码[xx] in sys_set:
                    sys_ss.add(x_sys.代码[xx])
            x_sql = f"""select *,DATE('{dta}') as data from company a inner join  (select symbol, max(cjl+0)/min(cjl+0) as fl from history where data <= '{dta}' and data >= (select distinct data from history where data <= '{dta}' order by data desc limit 1 offset 10) group by symbol) b on
                 a.代码 = b.symbol where a.代码 in ('{"','".join(sys_ss)}') order by data DESC,fl"""
            csv_date = query(x_sql)
            try:
                update(f"""delete from world.xl_result where data = '{dta}'""")
            except Exception as r:
                print(r)
            csv_date.to_sql(f'xl_result', con=engine(), if_exists='append', index=False)
            print(csv_date[['代码', '名称', '量比', '涨跌幅', 'fl']])
    except Exception as r:
        print(r)
    input()
